package student;

import Util.DBUtil;

import java.io.Serializable;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 汤舒青
 * @Version 1.0
 */


public class StudentDao implements Serializable {
    /**
     * 增加学生
     * @param s
     * @return
     */
    public int add(Student s) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "insert into s_student (sname,sex,telephone,qq,wechat,gradeid,enterdate,fromcity,birthday,address,pass,idcardpic)values (?,?,?,?,?,?,?,?,?,?,?,?)";
            System.out.println(sql);
            st = connection.prepareStatement(sql);
            st.setString(1, s.getSname());
            st.setString(2, s.getSex());
            st.setString(3, s.getTelephone());
            st.setString(4, s.getQq());
            st.setString(5, s.getWechat());
            st.setInt(6, s.getGradeid());
            st.setDate(7, Date.valueOf(s.getEnterdate()));
            st.setString(8, s.getFromcity());
            st.setDate(9, Date.valueOf(s.getBrithday()));
            st.setString(10, s.getAddress());
            st.setString(11, s.getPass());
            st.setString(12, s.getIdcardpic());
            return st.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(null, st, connection);
        }
        return 0;
    }

    /**
     * 修改信息
     * @param s
     * @return
     */

    public int update(Student s) {
        Connection con = null;
        PreparedStatement st = null;


        String sql = "update s_student set sname=?,sex=?,telephone=?,qq=?,wechat=?,gradeid=?,enterdate=?,fromcity=?,birthday=?,address=?,pass=?,idcardpic=? where stuno=?";
        System.out.println(sql);
        try {
            con = DBUtil.getConnection();
           st = con.prepareStatement(sql);
            st.setString(1, s.getSname());
            st.setString(2, s.getSex());
            st.setString(3, s.getTelephone());
            st.setString(4, s.getQq());
            st.setString(5, s.getWechat());
            st.setInt(6, s.getGradeid());
            st.setDate(7, Date.valueOf(s.getEnterdate()));
            st.setString(8, s.getFromcity());
            st.setDate(9, Date.valueOf(s.getBrithday()));
            st.setString(10, s.getAddress());
            st.setString(11, s.getPass());
            st.setString(12, s.getIdcardpic());
            st.setLong(13,s.getStuno());
            return st.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(st);
            DBUtil.close(con);
        }

        return 0;
    }

    /**
     *
     * 删除学生信息
     * @param id
     * @return
     */
    public int del(long id) {
        Connection con = null;
        PreparedStatement pe = null;

        String sql = "delete FROM  s_student where stuno=?";
        System.out.println(sql);
        try {
            pe = con.prepareStatement(sql);
            pe.setLong(1, id);
            return pe.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(pe);
            DBUtil.close(con);
        }

        return 0;
    }

    /**
     * 根据主键找到对象
     * @param id
     * @return
     */
    public Student get(long id) {

        PreparedStatement pe = null;
        ResultSet res = null;
        Connection con = DBUtil.getConnection();
        String sql = "select id,subjectname,classhour,gradeid  FROM  s_subject where id = ?";
        System.out.println(sql);
        try {

            pe = con.prepareStatement(sql);
            pe.setLong(1, id);
            res = pe.executeQuery();
            while (res.next()) {
                return new Student(res.getLong(1), res.getString(2), res.getString(3),res.getString(4),res.getString(5),res.getString(6),res.getInt(7),res.getString(8),res.getString(9),res.getString(10),res.getString(11),res.getString(12),res.getString(13));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(res);
            DBUtil.close(pe);
            DBUtil.close(con);
        }


        return null;
    }

    /**
     * 分页输出信息
     * @param pageNo
     * @param pageSize
     * @return
     */
    public List<Student> search(int pageNo, int pageSize) {
        String sql = "select * from s_student  order by stuno asc limit ?,?";
        List<Student> datas = new ArrayList<>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet res = null;

        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(sql);
            ps.setInt(1, (pageNo - 1) * pageSize);
            ps.setInt(2, pageSize);
            res = ps.executeQuery();
            for (int i = 1; i <= pageSize && res.next(); i++){
                datas.add(new Student(res.getLong(1), res.getString(2), res.getString(3),res.getString(4),res.getString(5),res.getString(6),res.getInt(7),res.getString(8),res.getString(9),res.getString(10),res.getString(11),res.getString(12),res.getString(13)));

            }

        } catch (SQLException e) {

            e.printStackTrace();
        } finally {
            DBUtil.close(res, ps, con);
        }
        return datas;
    }




}
